﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.Configuration;
using System.ComponentModel.DataAnnotations;
using trangbaocao.Models;
using System.Collections.ObjectModel;
using System.Data.OracleClient;
namespace trangbaocao.Models
{
    
    //public class FuelStationsAssociateModel
    //{
    //    public int id { get; set; }
    //    public string name { get; set; }
    //    public string dia_chi { get; set; }
    //    public string ten_trung_tam { get; set; }
        
    //    public string tien_dien_thang { get; set; }
    //    [DisplayFormat(DataFormatString = "{0:C}")]
    //    public decimal luy_ke_tien { get; set; }
    //    public string ngay_nhap { get; set; }
    //    public string ghi_chu { get; set; }
    //    public int stations_id { get;set;}
    //    public int main_stations_id { get; set; }

    //    public int nam { get; set; }
    //    public int thang { get; set; }
    //    public string ngay_cap_nhat { get; set; }
    //    [Required]
    //    [Range(0, 100000000, ErrorMessage = "Tiền điện tháng phải là số và không quá 100.000.000 đ")]
    //    public string tong_tien { get; set; }
    //    public List<FuelStationsAssociateModel> getFuelStationsIndex(string strdetail="")
    //    {
    //        List<FuelStationsAssociateModel> FuelStations = new List<FuelStationsAssociateModel>();
    //        string sql = "";
    //        if (HttpContext.Current.Session != null)
    //        {
    //            int quyen_id = Convert.ToInt16(HttpContext.Current.Session["quyen_id"]);
    //            int trungtam_id = Convert.ToInt16(HttpContext.Current.Session["trungtam_id"]);
    //            if (quyen_id > 2)
    //            {
    //                sql = "select b.id stations_id,b.name,b.dia_chi,nvl(a.tong_tien,0) tien_dien_thang,nvl(a.luy_ke_tien,0) luy_ke_tien,a.ngay_nhap,a.ghi_chu,c.name ten_trung_tam,c.id main_stations_id,nvl(a.id,0) id,nvl(a.thang,0) thang  from";
    //                sql += " (select a.stations_id,b.device_stations_id,b.muc_dung,b.don_gia,b.luy_ke_tien,b.ngay_nhap,b.ghi_chu,b.tong_tien,b.id,b.thang from(";
    //                //sql += " select max(id) id,stations_id from fuel where device_stations_id is null  " + strdetail + " group by stations_id";
    //                sql += " Select B.Id, A.stations_id, A.thang From ( select stations_id,max(thang) thang from fuel Where  device_stations_id is null "+strdetail+" group by stations_id) A, fuel B";
    //                sql += " where device_stations_id is null  And  A.thang=B.thang And A.stations_id=B.stations_id  and B.nam="+DateTime.Now.Year;
    //                sql += " )A,fuel b where a.id=b.id ) A,stations B,main_stations C where c.id=" + trungtam_id + " and b.main_stations_id=c.id and a.stations_id(+)=b.id order by c.name,b.name";
    //            }
    //            else
    //            {
    //                sql = "select b.id stations_id,b.name,b.dia_chi,nvl(a.tong_tien,0) tien_dien_thang,nvl(a.luy_ke_tien,0) luy_ke_tien,a.ngay_nhap,a.ghi_chu,c.name ten_trung_tam,c.id main_stations_id,nvl(a.id,0) id,nvl(a.thang,0) thang  from";
    //                sql += " (select a.stations_id,b.device_stations_id,b.muc_dung,b.don_gia,b.luy_ke_tien,b.ngay_nhap,b.ghi_chu,b.tong_tien,b.id,b.thang from(";
    //                //sql += " select max(id) id,stations_id from fuel where device_stations_id is null  " + strdetail + " group by stations_id";
    //                sql += " Select B.Id, A.stations_id, A.thang From ( select stations_id,max(thang) thang from fuel Where device_stations_id is null " + strdetail + " group by stations_id) A, fuel B";
    //                sql += " where device_stations_id is null  And  A.thang=B.thang And A.stations_id=B.stations_id  and B.nam=" + DateTime.Now.Year;
    //                sql += " )A,fuel b where a.id=b.id ) A,stations B,main_stations C where b.main_stations_id=c.id and a.stations_id(+)=b.id order by c.name,b.name";
    //            }
    //        }
    //        else
    //        {
    //            sql = "select b.id stations_id,b.name,b.dia_chi,nvl(a.tong_tien,0) tien_dien_thang,nvl(a.luy_ke_tien,0) luy_ke_tien,a.ngay_nhap,a.ghi_chu,c.name ten_trung_tam,c.id main_stations_id,nvl(a.id,0) id,nvl(a.thang,0) thang  from";
    //            sql += " (select a.stations_id,b.device_stations_id,b.muc_dung,b.don_gia,b.luy_ke_tien,b.ngay_nhap,b.ghi_chu,b.tong_tien,b.id,b.thang from(";
    //            //sql += " select max(id) id,stations_id from fuel where device_stations_id is null  " + strdetail + " group by stations_id";
    //            sql += " Select B.Id, A.stations_id, A.thang From ( select stations_id,max(thang) thang from fuel Where  device_stations_id is null " + strdetail + " group by stations_id) A, fuel B";
    //            sql += " where device_stations_id is null  And  A.thang=B.thang And A.stations_id=B.stations_id  and B.nam=" + DateTime.Now.Year;
    //            sql += " )A,fuel b where a.id=b.id ) A,stations B,main_stations C where  b.main_stations_id=c.id and a.stations_id(+)=b.id order by c.name,b.name";
    //        }
    //        OracleDataReader odr = General.Lib_GetDataReader(sql);
    //        while (odr.Read())
    //        {
    //            FuelStations.Add(new FuelStationsAssociateModel { stations_id = Convert.ToInt32(odr["stations_id"].ToString()), name = odr["name"].ToString(), dia_chi = odr["dia_chi"].ToString(), tien_dien_thang = odr["tien_dien_thang"].ToString(), luy_ke_tien = Convert.ToDecimal(odr["luy_ke_tien"].ToString()), ngay_nhap = odr.IsDBNull(5) ? null : odr.GetDateTime(5).ToString("dd/MM/yyyy"), ghi_chu = odr["ghi_chu"].ToString(), ten_trung_tam = odr["ten_trung_tam"].ToString(), main_stations_id = Convert.ToInt16(odr["main_stations_id"].ToString()), id = Convert.ToInt32(odr["id"].ToString()), thang = Convert.ToInt32(odr["thang"].ToString()) });
    //        }
    //        return FuelStations;
    //    }

    //    public List<FuelStationsAssociateModel> getFuelStationsDetail(string stations_id,string thang,string nam)
    //    {
    //        List<FuelStationsAssociateModel> FuelStations = new List<FuelStationsAssociateModel>();
    //        string sql = "";        
    //        sql+= "select a.id,b.name ten_tram,b.dia_chi,c.name ten_trung_tam,a.tong_tien,a.luy_ke_tien,a.ngay_nhap,a.ghi_chu,a.ngay_cap_nhat,a.thang,a.nam,a.stations_id"; 
    //        sql+= " from fuel a,stations b, main_stations c  ";
    //        sql += " where a.stations_id=b.id and a.main_stations_id=c.id and a.stations_id="+stations_id+" and a.nam="+nam+" and a.thang<="+thang+" and a.device_stations_id is null order by a.thang";
    //        OracleDataReader odr = General.Lib_GetDataReader(sql);
    //        while (odr.Read())
    //        {
    //            FuelStations.Add(new FuelStationsAssociateModel { id = Convert.ToInt32(odr["id"].ToString()), name = odr["ten_tram"].ToString(), dia_chi = odr["dia_chi"].ToString(), ten_trung_tam = odr["ten_trung_tam"].ToString(), tong_tien = odr["tong_tien"].ToString(), luy_ke_tien = Convert.ToDecimal(odr["luy_ke_tien"].ToString()), 
    //            ngay_nhap = odr.IsDBNull(6) ? null : odr.GetDateTime(6).ToString("dd/MM/yyyy"),ghi_chu = odr["ghi_chu"].ToString(), ngay_cap_nhat = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"),thang= Convert.ToInt32(odr["thang"].ToString()), nam= Convert.ToInt32(odr["nam"].ToString()),stations_id= Convert.ToInt32(odr["stations_id"].ToString())});
    //        }
    //        return FuelStations;
    //    }
    //    public List<FuelStationsAssociateModel> getFuelStations_Detail(FuelStationsAssociateModel FuelStations_)
    //    {
    //        List<FuelStationsAssociateModel> FuelStations = new List<FuelStationsAssociateModel>();
    //        string sql = "";
    //        sql += "select a.id,b.name ten_tram,b.dia_chi,c.name ten_trung_tam,a.tong_tien,a.luy_ke_tien,a.ngay_nhap,a.ghi_chu,a.ngay_cap_nhat,a.thang,a.nam,a.stations_id";
    //        sql += " from fuel a,stations b, main_stations c  ";
    //        sql += " where a.stations_id=b.id and a.main_stations_id=c.id and a.stations_id=" + FuelStations_.stations_id + " and a.nam=" + FuelStations_.nam + " and a.thang>=" + FuelStations_.thang + " and a.device_stations_id is null order by a.thang";
    //        OracleDataReader odr = General.Lib_GetDataReader(sql);
    //        while (odr.Read())
    //        {
    //            FuelStations.Add(new FuelStationsAssociateModel
    //            {
    //                id = Convert.ToInt32(odr["id"].ToString()),
    //                name = odr["ten_tram"].ToString(),
    //                dia_chi = odr["dia_chi"].ToString(),
    //                ten_trung_tam = odr["ten_trung_tam"].ToString(),
    //                tong_tien = odr["tong_tien"].ToString(),
    //                luy_ke_tien = Convert.ToDecimal(odr["luy_ke_tien"].ToString()),
    //                ngay_nhap = odr.IsDBNull(6) ? null : odr.GetDateTime(6).ToString("dd/MM/yyyy"),
    //                ghi_chu = odr["ghi_chu"].ToString(),
    //                ngay_cap_nhat = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"),
    //                thang = Convert.ToInt32(odr["thang"].ToString()),
    //                nam = Convert.ToInt32(odr["nam"].ToString()),
    //                stations_id = Convert.ToInt32(odr["stations_id"].ToString())
    //            });
    //        }
    //        return FuelStations;
    //    }
    //    public List<FuelStationsAssociateModel> GetFuelStationsById(string stations_id, string thang, string nam)
    //    {
    //        List<FuelStationsAssociateModel> FuelStations = new List<FuelStationsAssociateModel>();
    //        string sql = "";
    //        sql += "select a.id,b.name ten_tram,b.dia_chi,c.name ten_trung_tam,a.tong_tien,a.luy_ke_tien,a.ngay_nhap,a.ghi_chu,a.ngay_cap_nhat,a.thang,a.nam,a.stations_id";
    //        sql += " from fuel a,stations b, main_stations c  ";
    //        sql += " where a.stations_id=b.id and a.main_stations_id=c.id and a.stations_id=" + stations_id + " and a.nam=" + nam + " and a.thang<=" + thang + " and a.device_stations_id is null order by a.thang";
    //        OracleDataReader odr = General.Lib_GetDataReader(sql);
    //        while (odr.Read())
    //        {
    //            FuelStations.Add(new FuelStationsAssociateModel
    //            {
    //                id = Convert.ToInt32(odr["id"].ToString()),
    //                name = odr["ten_tram"].ToString(),
    //                dia_chi = odr["dia_chi"].ToString(),
    //                ten_trung_tam = odr["ten_trung_tam"].ToString(),
    //                tong_tien = odr["tong_tien"].ToString(),
    //                luy_ke_tien = Convert.ToDecimal(odr["luy_ke_tien"].ToString()),
    //                ngay_nhap = odr.IsDBNull(6) ? null : odr.GetDateTime(6).ToString("dd/MM/yyyy"),
    //                ghi_chu = odr["ghi_chu"].ToString(),
    //                ngay_cap_nhat = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"),
    //                thang = Convert.ToInt32(odr["thang"].ToString()),
    //                nam = Convert.ToInt32(odr["nam"].ToString()),
    //                stations_id = Convert.ToInt32(odr["stations_id"].ToString())
    //            });
    //        }
    //        return FuelStations;
    //    }
    //    public FuelStationsAssociateModel GetFuelStationsBy_Id(string id_)
    //    {
    //        FuelStationsAssociateModel FuelStations = new FuelStationsAssociateModel();
    //        string sql = "";
    //        sql += "select a.id,b.name ten_tram,b.dia_chi,c.name ten_trung_tam,a.tong_tien,a.luy_ke_tien,a.ngay_nhap,a.ghi_chu,a.ngay_cap_nhat,a.thang,a.nam,a.stations_id";
    //        sql += " from fuel a,stations b,main_stations c where ";
    //        sql += "  a.stations_id=b.id and a.main_stations_id=c.id and a.id="+id_;
    //        OracleDataReader odr = General.Lib_GetDataReader(sql);
    //        while (odr.Read())
    //        {
    //            FuelStations.id = Convert.ToInt32(odr["id"].ToString());
    //            FuelStations.name = odr["ten_tram"].ToString();
    //            FuelStations.dia_chi = odr["dia_chi"].ToString();
    //            FuelStations.ten_trung_tam = odr["ten_trung_tam"].ToString();
    //            FuelStations.tong_tien = odr["tong_tien"].ToString();
    //            FuelStations.luy_ke_tien = Convert.ToDecimal(odr["luy_ke_tien"].ToString());
    //            FuelStations.ngay_nhap = odr.IsDBNull(6) ? null : odr.GetDateTime(6).ToString("dd/MM/yyyy");
    //            FuelStations.ghi_chu = odr["ghi_chu"].ToString();
    //            FuelStations.ngay_cap_nhat = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy");
    //            FuelStations.thang = Convert.ToInt32(odr["thang"].ToString());
    //            FuelStations.nam = Convert.ToInt32(odr["nam"].ToString());
    //            FuelStations.stations_id = Convert.ToInt32(odr["stations_id"].ToString());
                
    //        }
    //        return FuelStations;
    //    }
    //    public void updateFuelStationsDetail(FuelStationsAssociateModel FuelStations)
    //    {
    //        string sql = "UPDATE fuel set luy_ke_tien="+FuelStations.luy_ke_tien+", tong_tien=" + FuelStations.tong_tien + ",ngay_cap_nhat=to_date('" + DateTime.Now.ToString("dd/MM/yyyy") + "','dd/mm/yyyy'),ghi_chu='"+FuelStations.ghi_chu+"' where id="+FuelStations.id;
    //        General.Lib_ExecuteNonQuery(sql);
    //    }
    //    public int checkFuelStations(int stations_id,int thang)
    //    {
    //        string sql = "select * from fuel where device_stations_id is null and stations_id="+ stations_id +" and thang>"+thang;
    //        return General.Lib_CheckExist(sql);
    //    }
    //    public void updateLuyke_FuelStations(FuelStationsAssociateModel FuelStations)
    //    {
    //        string sql = "UPDATE fuel set luy_ke_tien=" + FuelStations.luy_ke_tien + " where id=" + FuelStations.id;
    //        General.Lib_ExecuteNonQuery(sql);
    //    }
    //    public List<FuelStationsAssociateModel> FindFuelStations(string str, string year, string month)
    //    {
    //        string sql = "select b.id stations_id,b.name,b.dia_chi,nvl(a.tong_tien,0) tien_dien_thang,nvl(a.luy_ke_tien,0) luy_ke_tien,a.ngay_nhap,a.ghi_chu,c.name ten_trung_tam,c.id main_stations_id,nvl(a.id,0) id,nvl(a.thang,0) thang from";
    //        sql += " (select a.stations_id,b.device_stations_id,b.muc_dung,b.don_gia,b.luy_ke_tien,b.ngay_nhap,b.ghi_chu,b.tong_tien,b.id,b.thang from(";
    //        //sql += " (select max(id) id,stations_id from fuel where nam=" + year + " and thang<=" + month + " and device_stations_id is null group by stations_id";
    //        sql += " Select B.Id, A.stations_id, A.thang From ( select stations_id,max(thang) thang from fuel Where nam=" + year + " and thang<=" + month + " and device_stations_id is null group by stations_id) A, fuel B";
    //        sql += " where device_stations_id is null  And  A.thang=B.thang And A.stations_id=B.stations_id  and B.nam=" + year;
    //        sql += " )A,fuel b where a.id=b.id ) A,stations B,main_stations C where b.main_stations_id=c.id and a.stations_id(+)=b.id and " + str + " order by c.name,b.name";
    //        List<FuelStationsAssociateModel> FuelStations = new List<FuelStationsAssociateModel>();
    //        OracleDataReader odr = General.Lib_GetDataReader(sql);
    //        while (odr.Read())
    //        {
    //            FuelStations.Add(new FuelStationsAssociateModel { stations_id = Convert.ToInt32(odr["stations_id"].ToString()), name = odr["name"].ToString(), dia_chi = odr["dia_chi"].ToString(), tien_dien_thang = odr["tien_dien_thang"].ToString(), luy_ke_tien = Convert.ToDecimal(odr["luy_ke_tien"].ToString()), ngay_nhap = odr.IsDBNull(5) ? null : odr.GetDateTime(5).ToString("dd/MM/yyyy"), ghi_chu = odr["ghi_chu"].ToString(), ten_trung_tam = odr["ten_trung_tam"].ToString(), main_stations_id = Convert.ToInt16(odr["main_stations_id"].ToString()), id = Convert.ToInt32(odr["id"].ToString()), thang = Convert.ToInt32(odr["thang"].ToString()) });
    //        }
    //        return FuelStations;
    //    }
    //    public void updateFuelStations(FuelStationsAssociateModel FuelStations)
    //    {            
    //        string sql = "INSERT INTO Fuel(id,don_vi_tinh,fuel_type_id,luy_ke_tien,ngay_nhap,ghi_chu,stations_id,main_stations_id,ngay_cap_nhat,thang,nam,tong_tien)";            
    //        sql += " select nvl((select max(id)+1 from fuel),1),'lit',3";
    //        sql += "," + FuelStations.luy_ke_tien + ",to_date('" + FuelStations.ngay_nhap + "','dd/mm/yyyy')";
    //        sql += ",'" + FuelStations.ghi_chu + "'," + FuelStations.stations_id + "," + FuelStations.main_stations_id + ",to_date('" + FuelStations.ngay_cap_nhat + "','dd/mm/yyyy')," + FuelStations.thang + "," + FuelStations.nam + "," + FuelStations.tong_tien + " from dual";

    //        General.Lib_ExecuteNonQuery(sql);
    //    }
    //}
    public class FuelGeneratorAssociateModel
    {
        public int id { get; set; }
        public int device_stations_id { get; set; }
        public string name { get; set; }
        public string ma_he_thong { get; set; }
        public string ten_tram { get; set; }
        public string dia_chi { get; set; }
        public string ten_trung_tam { get; set; }
        public string ngay_nhap { get; set; }
        public string ngay_cap_nhat { get; set; }
        public string user_cap_nhat { get; set; }
        public string ghi_chu { get; set; }
        [Required(ErrorMessage = "Số lít không được để trống!")]
        //[Range(0, 1000, ErrorMessage = "Dầu nhập trong tháng phải là số và không quá 1000 lít")]
        public string nhap_trong_thang { get; set; }
        [Required]
        //[Range(0, 100000, ErrorMessage = "Đơn giá phải là số và không quá 100.000 đ/lít")]
        public int stations_id { get; set; }
        public int main_stations_id { get; set; }
        public int nam { get; set; }
        public int thang { get; set; }
        public int fuel_status { get; set; }

        //public string don_gia { get; set; }
        //public string tong_tien_thang { get; set; }
        //[DisplayFormat(DataFormatString = "{0:C}")]
        //public decimal luy_ke_tien { get; set; }
        //public int luy_ke_nhien_lieu { get; set; }
        //public string luy_ke_tien { get; set; }
        //public int luy_ke_thoi_gian { get; set; }
        //[Range(0, 1000, ErrorMessage = "Mức dùng trong tháng phải là số và không quá 1000 lít")]
        //public string muc_dung { get; set; }
        //[Range(0, 1000, ErrorMessage = "Thời gian tiêu thụ nhiên liệu trong tháng phải là số và không quá 1000 giờ")]
        //public string thoi_gian_chay { get; set; }
        
        public List<FuelGeneratorAssociateModel> getFuelGeneratorIndex(string nam, string thang)
        {

            List<FuelGeneratorAssociateModel> FuelGenerator = new List<FuelGeneratorAssociateModel>();
            string sql = "";
            if (HttpContext.Current.Session != null)
            {
                int quyen_id = Convert.ToInt16(HttpContext.Current.Session["quyen_id"]);
                int trungtam_id = Convert.ToInt16(HttpContext.Current.Session["trungtam_id"]);
                if (quyen_id > 2)
                {
                    sql = "select a.device_stations_id,a.ma_he_thong,a.name,c.name ten_tram,c.dia_chi,b.name ten_trung_tam,a.nhap_trong_thang,a.ngay_nhap,a.ngay_cap_nhat,a.user_cap_nhat,nvl(a.thang,0) thang,nvl(a.nam,0) nam,a.ghi_chu,b.id main_stations_id,c.id stations_id from";
                    sql += "(";
                    sql += "   select b.id device_stations_id,b.ma_he_thong,b.name,b.main_stations_id,b.stations_id,a.nhap_trong_thang,a.ngay_nhap,a.ngay_cap_nhat,a.user_cap_nhat,a.thang,a.nam,a.ghi_chu from";
                    sql += "   (";
                    sql += "        select b.device_stations_id,b.main_stations_id,b.stations_id,b.ngay_nhap,b.ngay_cap_nhat,b.nhap_trong_thang,b.user_cap_nhat,b.thang,b.nam,b.ghi_chu from";
                    sql += "        (            ";
                    sql += "            Select B.Id, B.stations_id, B.thang ";
                    sql += "            from( select max(id) id,device_stations_id from fuel  where nam=" + nam + " and thang=" + thang + " group by device_stations_id) a, fuel b where a.id=b.id";
                    sql += "        )A,fuel b where a.id=b.id";
                    sql += "    ) a, device_stations b where  a.device_stations_id(+)=b.id and b.main_sort_device_id=9";
                    sql += ")";
                    sql += "a,main_stations b,stations c where b.id=" + trungtam_id + " and a.main_stations_id=b.id and a.stations_id=c.id order by b.name,c.name,a.name";
                }
                else
                {
                    sql = "select a.device_stations_id,a.ma_he_thong,a.name,c.name ten_tram,c.dia_chi,b.name ten_trung_tam,a.nhap_trong_thang,a.ngay_nhap,a.ngay_cap_nhat,a.user_cap_nhat,nvl(a.thang,0) thang,nvl(a.nam,0) nam,a.ghi_chu,b.id main_stations_id,c.id stations_id from";
                    sql += "(";
                    sql += "   select b.id device_stations_id,b.ma_he_thong,b.name,b.main_stations_id,b.stations_id,a.nhap_trong_thang,a.ngay_nhap,a.ngay_cap_nhat,a.user_cap_nhat,a.thang,a.nam,a.ghi_chu from";
                    sql += "   (";
                    sql += "        select b.device_stations_id,b.main_stations_id,b.stations_id,b.ngay_nhap,b.ngay_cap_nhat,b.nhap_trong_thang,b.user_cap_nhat,b.thang,b.nam,b.ghi_chu from";
                    sql += "        (            ";
                    sql += "            Select B.Id, B.stations_id, B.thang ";
                    sql += "            from( select max(id) id,device_stations_id from fuel  where nam=" + nam + " and thang=" + thang + " group by device_stations_id) a, fuel b where a.id=b.id";
                    sql += "        )A,fuel b where a.id=b.id";
                    sql += "    ) a, device_stations b where  a.device_stations_id(+)=b.id and b.main_sort_device_id=9";
                    sql += ")";
                    sql += "a,main_stations b,stations c where a.main_stations_id=b.id and a.stations_id=c.id order by b.name,c.name,a.name";
        
    
                }
            }
            else
            {
                sql = "select a.device_stations_id,a.ma_he_thong,a.name,c.name ten_tram,c.dia_chi,b.name ten_trung_tam,a.nhap_trong_thang,a.ngay_nhap,a.ngay_cap_nhat,a.user_cap_nhat,nvl(a.thang,0) thang,nvl(a.nam,0) nam,a.ghi_chu,b.id main_stations_id,c.id stations_id from";
                sql += "(";
                sql += "   select b.id device_stations_id,b.ma_he_thong,b.name,b.main_stations_id,b.stations_id,a.nhap_trong_thang,a.ngay_nhap,a.ngay_cap_nhat,a.user_cap_nhat,a.thang,a.nam,a.ghi_chu from";
                sql += "   (";
                sql += "        select b.device_stations_id,b.main_stations_id,b.stations_id,b.ngay_nhap,b.ngay_cap_nhat,b.nhap_trong_thang,b.user_cap_nhat,b.thang,b.nam,b.ghi_chu from";
                sql += "        (            ";
                sql += "            Select B.Id, B.stations_id, B.thang ";
                sql += "            from( select max(id) id,device_stations_id from fuel  where nam=" + nam + " and thang=" + thang + " group by device_stations_id) a, fuel b where a.id=b.id";
                sql += "        )A,fuel b where a.id=b.id";
                sql += "    ) a, device_stations b where  a.device_stations_id(+)=b.id and b.main_sort_device_id=9";
                sql += ")";
                sql += "a,main_stations b,stations c where a.main_stations_id=b.id and a.stations_id=c.id order by b.name,c.name,a.name";
            }
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                FuelGenerator.Add(new FuelGeneratorAssociateModel
                {
                    device_stations_id = Convert.ToInt32(odr["device_stations_id"].ToString()),
                    ma_he_thong = odr["ma_he_thong"].ToString(),
                    name = odr["name"].ToString(),
                    ten_trung_tam = odr["ten_trung_tam"].ToString(),
                    ten_tram = odr["ten_tram"].ToString(),
                    dia_chi = odr["dia_chi"].ToString(),
                    nhap_trong_thang = odr["nhap_trong_thang"].ToString(),
                    ngay_nhap = odr.IsDBNull(7) ? null : odr.GetDateTime(7).ToString("dd/MM/yyyy"),
                    ngay_cap_nhat = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"),
                    user_cap_nhat=odr["user_cap_nhat"].ToString(),
                    ghi_chu = odr["ghi_chu"].ToString(),
                    stations_id = Convert.ToInt32(odr["stations_id"].ToString()),
                    main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                    thang = Convert.ToInt32(odr["thang"].ToString()),
                    nam = Convert.ToInt32(odr["nam"].ToString())
                }); 
            }

            return FuelGenerator;
        }
        public List<FuelGeneratorAssociateModel> FindFuelGenerator(string str, string year, string month)
        {
            string sql = "";
            sql = "select a.device_stations_id,a.ma_he_thong,a.name,a.name ten_tram,c.dia_chi,b.name ten_trung_tam,a.nhap_trong_thang,a.ngay_nhap,a.ngay_cap_nhat,a.user_cap_nhat,nvl(a.thang,0) thang,nvl(a.nam,0) nam,a.ghi_chu,b.id main_stations_id,c.id stations_id from";
            sql += "(";
            sql += "   select b.id device_stations_id,b.ma_he_thong,b.name,b.main_stations_id,b.stations_id,a.nhap_trong_thang,a.ngay_nhap,a.ngay_cap_nhat,a.user_cap_nhat,a.thang,a.nam,a.ghi_chu from";
            sql += "   (";
            sql += "        select b.device_stations_id,b.main_stations_id,b.stations_id,b.ngay_nhap,b.ngay_cap_nhat,b.nhap_trong_thang,b.user_cap_nhat,b.thang,b.nam,b.ghi_chu from";
            sql += "        (            ";
            sql += "            Select B.Id, B.stations_id, B.thang ";
            sql += "            from( select max(id) id,device_stations_id from fuel  where nam=" + year + " and thang=" + month + " group by device_stations_id) a, fuel b where a.id=b.id";
            sql += "        )A,fuel b where a.id=b.id";
            sql += "    ) a, device_stations b where  a.device_stations_id(+)=b.id and b.main_sort_device_id=9";
            sql += ")";
            sql += "a,main_stations b,stations c where a.main_stations_id=b.id and a.stations_id=c.id and "+str+" order by b.name,c.name,a.name";
            List<FuelGeneratorAssociateModel> FuelGenerator = new List<FuelGeneratorAssociateModel>();
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                FuelGenerator.Add(new FuelGeneratorAssociateModel
                {
                    device_stations_id = Convert.ToInt32(odr["device_stations_id"].ToString()),
                    ma_he_thong = odr["ma_he_thong"].ToString(),
                    name = odr["name"].ToString(),
                    ten_trung_tam = odr["ten_trung_tam"].ToString(),
                    ten_tram = odr["ten_tram"].ToString(),
                    dia_chi = odr["dia_chi"].ToString(),
                    nhap_trong_thang = odr["nhap_trong_thang"].ToString(),
                    ngay_nhap = odr.IsDBNull(7) ? null : odr.GetDateTime(7).ToString("dd/MM/yyyy"),
                    ngay_cap_nhat = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"),
                    user_cap_nhat = odr["user_cap_nhat"].ToString(),
                    ghi_chu = odr["ghi_chu"].ToString(),
                    stations_id = Convert.ToInt32(odr["stations_id"].ToString()),
                    main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                    thang = Convert.ToInt32(odr["thang"].ToString()),
                    nam = Convert.ToInt32(odr["nam"].ToString())
                });
            }
            return FuelGenerator;
        }

        public List<FuelGeneratorAssociateModel> getFuelGeneratorUseList(string device_stations_id, string nam, string thang)
        {
            List<FuelGeneratorAssociateModel> FuelGenerator = new List<FuelGeneratorAssociateModel>();
            string sql = "";
            if (HttpContext.Current.Session != null)
            {
                int quyen_id = Convert.ToInt16(HttpContext.Current.Session["quyen_id"]);
                int trungtam_id = Convert.ToInt16(HttpContext.Current.Session["trungtam_id"]);
                if (quyen_id > 2)
                {
                    sql = "select a.id,b.id device_stations_id,b.ma_he_thong,b.name,c.name ten_tram,c.dia_chi,d.name ten_trung_tam,a.nhap_trong_thang,";
                    sql += " a.ngay_nhap,a.ngay_cap_nhat,a.user_cap_nhat,a.ghi_chu,a.thang,a.nam,c.id stations_id,d.id main_stations_id,a.fuel_status";
                    sql += " from fuel a,device_stations b,stations c, main_stations d";
                    sql += " where a.main_stations_id=d.id and a.stations_id=c.id";
                    sql += " and a.device_stations_id=b.id and a.thang=" + thang + " and a.nam=" + nam + " and a.device_stations_id=" + device_stations_id + " and d.id=" + trungtam_id + " order by b.name,c.name,d.name,a.ngay_nhap";

                }
                else
                {
                    sql = "select a.id,b.id device_stations_id,b.ma_he_thong,b.name,c.name ten_tram,c.dia_chi,d.name ten_trung_tam,a.nhap_trong_thang,";
                    sql += " a.ngay_nhap,a.ngay_cap_nhat,a.user_cap_nhat,a.ghi_chu,a.thang,a.nam,c.id stations_id,d.id main_stations_id,a.fuel_status";
                    sql += " from fuel a,device_stations b,stations c, main_stations d";
                    sql += " where a.main_stations_id=d.id and a.stations_id=c.id";
                    sql += " and a.device_stations_id=b.id and a.thang=" + thang + " and a.nam=" + nam + " and a.device_stations_id=" + device_stations_id + " order by b.name,c.name,d.name,a.ngay_nhap";
                }
            }
            else
            {
                sql = "select a.id,b.id device_stations_id,b.ma_he_thong,b.name,c.name ten_tram,c.dia_chi,d.name ten_trung_tam,a.nhap_trong_thang,";
                sql += " a.ngay_nhap,a.ngay_cap_nhat,a.user_cap_nhat,a.ghi_chu,a.thang,a.nam,c.id stations_id,d.id main_stations_id,a.fuel_status";
                 sql +=" from fuel a,device_stations b,stations c, main_stations d";
                 sql +=" where a.main_stations_id=d.id and a.stations_id=c.id" ;
                 sql += " and a.device_stations_id=b.id and a.thang=" + thang + " and a.nam=" + nam + " and a.device_stations_id=" + device_stations_id + " order by b.name,c.name,d.name,a.ngay_nhap";
            }
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
               
                FuelGenerator.Add(new FuelGeneratorAssociateModel
                {
                    id = Convert.ToInt32(odr["id"].ToString()),
                    device_stations_id = Convert.ToInt32(odr["device_stations_id"].ToString()),
                    ma_he_thong = odr["ma_he_thong"].ToString(),
                    name = odr["name"].ToString(),
                    ten_tram = odr["ten_tram"].ToString(),
                    dia_chi = odr["dia_chi"].ToString(),
                    ten_trung_tam = odr["ten_trung_tam"].ToString(),
                    nhap_trong_thang = odr["nhap_trong_thang"].ToString(),
                    ngay_nhap = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"),
                    ngay_cap_nhat = odr.IsDBNull(9) ? null : odr.GetDateTime(9).ToString("dd/MM/yyyy"),
                    user_cap_nhat = odr["user_cap_nhat"].ToString(),
                    ghi_chu = odr["ghi_chu"].ToString(),
                    stations_id = Convert.ToInt32(odr["stations_id"].ToString()),
                    main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                    thang = Convert.ToInt32(odr["thang"].ToString()),
                    nam = Convert.ToInt32(odr["nam"].ToString()),
                    fuel_status = Convert.ToInt32(odr["fuel_status"].ToString())
                });
            }
            return FuelGenerator;
        }
        public void updateFuelGeneratorUse(FuelGeneratorAssociateModel FuelGenerator)
        {
            string nhap_trong_thang = FuelGenerator.nhap_trong_thang;
            if (FuelGenerator.nhap_trong_thang.IndexOf(',') >= 0)
                nhap_trong_thang = FuelGenerator.nhap_trong_thang.Replace(',', '.');
            
            string sql = "update Fuel set ngay_cap_nhat=to_date('" + FuelGenerator.ngay_cap_nhat + "','dd/mm/yyyy'),ngay_nhap=to_date('" + FuelGenerator.ngay_nhap + "','dd/mm/yyyy'),";
            sql += "nhap_trong_thang=" + nhap_trong_thang + ",ghi_chu='" + FuelGenerator.ghi_chu + "',fuel_status="+FuelGenerator.fuel_status+" where id=" + FuelGenerator.id;
            General.Lib_ExecuteNonQuery(sql);
        }    
        public void deleteFuelGeneratorUse(int id)
        {
            string sql = "delete from fuel where id=" + id;
            General.Lib_ExecuteNonQuery(sql);
        }

        public void insertFuelGeneratorUse(FuelGeneratorAssociateModel FuelGenerator)
        {
            string sql = "INSERT INTO Fuel(id,main_stations_id,stations_id,device_stations_id,nhap_trong_thang,ngay_nhap,ngay_cap_nhat,ghi_chu,thang,nam,fuel_status)";
            sql += " select nvl((select max(id)+1 from Fuel),1)," + FuelGenerator.main_stations_id + "," + FuelGenerator.stations_id + "," + FuelGenerator.device_stations_id;
            sql += ","+FuelGenerator.nhap_trong_thang+",to_date('" + FuelGenerator.ngay_nhap + "','dd/mm/yyyy'),to_date('" + FuelGenerator.ngay_cap_nhat + "','dd/mm/yyyy')";
            sql += ",'" + FuelGenerator.ghi_chu + "'," + FuelGenerator.thang + "," + FuelGenerator.nam + ","+FuelGenerator.fuel_status+" from dual";
            General.Lib_ExecuteNonQuery(sql);
        }

        public List<FuelGeneratorModel> getGeneratorUseList(int main_stations_id, string nam, string thang)
        {

            List<FuelGeneratorModel> FuelGenerator = new List<FuelGeneratorModel>();
            string sql = "";
            if (HttpContext.Current.Session != null)
            {
                int quyen_id = Convert.ToInt16(HttpContext.Current.Session["quyen_id"]);
                int trungtam_id = Convert.ToInt16(HttpContext.Current.Session["trungtam_id"]);
                if (quyen_id > 2)
                {
                    sql = "select c.name ten_trung_tam,a.nhap_trong_thang,a.don_gia,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.id main_stations_id,";
                    sql += " nvl(a.id,0) id,nvl(a.thang,0) thang,nvl(a.nam,0) nam from fuel_input a, main_stations c WHERE";
                    sql += " a.fuel_type_id>1 and a.main_stations_id=c.id and c.id=" + trungtam_id + " and a.nam=" + nam + " and a.thang=" + thang + " order by a.ngay_nhap";
                }
                else
                {
                    sql = "select c.name ten_trung_tam,a.nhap_trong_thang,a.don_gia,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.id main_stations_id,";
                    sql += " nvl(a.id,0) id,nvl(a.thang,0) thang,nvl(a.nam,0) nam from fuel_input a, main_stations c WHERE";
                    sql += " a.fuel_type_id>1 and a.main_stations_id=c.id and c.id=" + main_stations_id + " and a.nam=" + nam + " and a.thang=" + thang + " order by a.ngay_nhap";
                }
            }
            else
            {
                sql = "select c.name ten_trung_tam,a.nhap_trong_thang,a.don_gia,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.id main_stations_id,";
                sql += " nvl(a.id,0) id,nvl(a.thang,0) thang,nvl(a.nam,0) nam from fuel_input a, main_stations c WHERE";
                sql += " a.fuel_type_id>1 and a.main_stations_id=c.id and c.id=" + main_stations_id + " and a.nam=" + nam + " and a.thang=" + thang + " order by a.ngay_nhap";
            }
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {

                if (odr["nam"].ToString() != nam)
                {
                    //if(Convert.ToInt32(thang)==1)
                    //{
                    FuelGenerator.Add(new FuelGeneratorModel
                    {
                        id = Convert.ToInt32(odr["id"].ToString()),
                        ten_trung_tam = odr["ten_trung_tam"].ToString(),
                        nhap_trong_thang = "0",
                        don_gia = "0",
                        tong_tien = "0",
                        ngay_nhap = odr.IsDBNull(4) ? null : odr.GetDateTime(4).ToString("dd/MM/yyyy"),
                        ghi_chu = "",
                        main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                        thang = Convert.ToInt32(odr["thang"].ToString()),
                        nam = Convert.ToInt32(odr["nam"].ToString())
                    });
                }
                else
                {
                    FuelGenerator.Add(new FuelGeneratorModel
                    {
                        id = Convert.ToInt32(odr["id"].ToString()),
                        ten_trung_tam = odr["ten_trung_tam"].ToString(),
                        nhap_trong_thang = odr["nhap_trong_thang"].ToString(),
                        don_gia = odr["don_gia"].ToString(),
                        tong_tien = odr["tong_tien"].ToString(),
                        ngay_nhap = odr.IsDBNull(4) ? null : odr.GetDateTime(4).ToString("dd/MM/yyyy"),
                        ghi_chu = odr["ghi_chu"].ToString(),
                        main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                        thang = Convert.ToInt32(odr["thang"].ToString()),
                        nam = Convert.ToInt32(odr["nam"].ToString())
                    });

                }
            }
            return FuelGenerator;
        }

        //public void updateFuelGenerator(FuelGeneratorAssociateModel FuelGenerator)
        //{
        //    string sql = "";
        //    string nhap_trong_thang = "_";
        //    string don_gia = "_";
        //    string muc_dung = "_";
        //    string thoi_gian_chay = "_";

        //    string nhap_trong_thang_ = "_";
        //    string don_gia_ = "_";
        //    string muc_dung_ = "_";
        //    string thoi_gian_chay_ = "_";
        //    if (FuelGenerator.nhap_trong_thang == null)
        //        nhap_trong_thang = "a";
        //    else
        //        nhap_trong_thang_ = FuelGenerator.nhap_trong_thang;
        //    if (FuelGenerator.don_gia == null)
        //        don_gia = "b";
        //    else
        //        don_gia_ = FuelGenerator.don_gia;
        //    if (FuelGenerator.muc_dung == null)
        //        muc_dung = "c";
        //    else
        //        muc_dung_ = FuelGenerator.muc_dung;
        //    if (FuelGenerator.thoi_gian_chay == null)
        //        thoi_gian_chay = "d";
        //    else
        //        thoi_gian_chay_ = FuelGenerator.thoi_gian_chay;
        //    sql += "INSERT INTO Fuel";
        //    //sql += " select nvl((select max(id)+1 from fuel),1)," + FuelGenerator.device_stations_id + "," + FuelGenerator.ton_dau_thang + "," + FuelGenerator.nhap_trong_thang + ",'lit',1," + FuelGenerator.don_gia;
        //    //sql += "," + FuelGenerator.muc_dung + "," + FuelGenerator.thoi_gian_chay + "," + FuelGenerator.luy_ke_nhien_lieu + "," + FuelGenerator.luy_ke_tien + "," + FuelGenerator.luy_ke_thoi_gian + ",to_date('" + FuelGenerator.ngay_nhap + "','dd/mm/yyyy')";
        //    sql += " select nvl((select max(id)+1 from fuel),1)," + FuelGenerator.device_stations_id + "," + FuelGenerator.ton_dau_thang + ",decode('" + nhap_trong_thang + "','a',null,'" + nhap_trong_thang_ + "'),'lit',1,decode('" + don_gia + "','b',null,'" + don_gia_ + "')";
        //    sql += ",decode('" + muc_dung + "','c',null,'" + muc_dung_ + "'),decode('" + thoi_gian_chay + "','d',null,'" + thoi_gian_chay_ + "')," + FuelGenerator.luy_ke_nhien_lieu + "," + FuelGenerator.luy_ke_tien + "," + FuelGenerator.luy_ke_thoi_gian + ",to_date('" + FuelGenerator.ngay_nhap + "','dd/mm/yyyy')";
        //    sql += ",'" + FuelGenerator.ghi_chu + "'," + FuelGenerator.stations_id + "," + FuelGenerator.main_stations_id + ",to_date('" + FuelGenerator.ngay_nhap + "','dd/mm/yyyy')," + FuelGenerator.thang + "," + FuelGenerator.nam + "," + FuelGenerator.tong_tien_thang + " from dual";

        //    General.Lib_ExecuteNonQuery(sql);
        //}
    }
    //-------------------------------------New Vesion-------------------------------------------------------------------------------
    public class FuelStationsModel
    {
        public int id { get; set; }
        public string name { get; set; }
        public string dia_chi { get; set; }
        public string ten_trung_tam { get; set; }
        [Required(ErrorMessage = "Số điện không được để trống!")]
        //[Range(0, 10000, ErrorMessage = "KW phải là số và không quá 10000 KW")]
        public string nhap_trong_thang { get; set; }
        public string don_gia { get; set; }

        [DisplayFormat(DataFormatString = "{0:C}")]
        public string ngay_nhap { get; set; }
        public string ngay_cap_nhat { get; set; }
        public string user_cap_nhat { get; set; }
        public string ghi_chu { get; set; }
        public int stations_id { get; set; }
        public int main_stations_id { get; set; }

        public int nam { get; set; }
        public int thang { get; set; }

        [Required(ErrorMessage = "Tiền điện tháng không được để trống!")]
        //[Range(0, 100000000, ErrorMessage = "Tiền điện tháng phải là số và không quá 100.000.000 đ")]
        public string tong_tien { get; set; }
        public List<FuelStationsModel> getFuelStationsIndex(string strdetail = "")
        {
            List<FuelStationsModel> FuelStations = new List<FuelStationsModel>();
            string sql = "";
            if (HttpContext.Current.Session != null)
            {
                int quyen_id = Convert.ToInt16(HttpContext.Current.Session["quyen_id"]);
                int trungtam_id = Convert.ToInt16(HttpContext.Current.Session["trungtam_id"]);
                if (quyen_id > 2)
                {
                    sql += " select b.id stations_id,b.name,b.dia_chi,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.name ten_trung_tam,c.id main_stations_id,nvl(a.id,0) id,nvl(a.thang,0) thang  from";
                    sql += " (select a.stations_id,b.don_gia,b.ngay_nhap,b.ghi_chu,b.tong_tien,b.id,b.thang from(";
                    sql += " Select B.Id, A.stations_id, A.thang From ( select stations_id,max(thang) thang from fuel_input Where fuel_type_id=1 "+strdetail+" group by stations_id) A, fuel_input B";
                    sql += " where fuel_type_id=1  And  A.thang=B.thang And A.stations_id=B.stations_id  and B.nam=" + DateTime.Now.Year;
                    sql += " )A,fuel_input b where a.id=b.id ) A,stations B,main_stations C where c.id=" + trungtam_id + " and b.main_stations_id=c.id and a.stations_id(+)=b.id order by c.name,b.name";
                }
                else
                {
                    sql += " select b.id stations_id,b.name,b.dia_chi,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.name ten_trung_tam,c.id main_stations_id,nvl(a.id,0) id,nvl(a.thang,0) thang  from";
                    sql += " (select a.stations_id,b.don_gia,b.ngay_nhap,b.ghi_chu,b.tong_tien,b.id,b.thang from(";
                    sql += " Select B.Id, A.stations_id, A.thang From ( select stations_id,max(thang) thang from fuel_input Where fuel_type_id=1 " + strdetail + " group by stations_id) A, fuel_input B";
                    sql += " where fuel_type_id=1  And  A.thang=B.thang And A.stations_id=B.stations_id  and B.nam=" + DateTime.Now.Year;
                    sql += " )A,fuel_input b where a.id=b.id ) A,stations B,main_stations C where b.main_stations_id=c.id and a.stations_id(+)=b.id order by c.name,b.name";
                }
            }
            else
            {
                sql += " select b.id stations_id,b.name,b.dia_chi,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.name ten_trung_tam,c.id main_stations_id,nvl(a.id,0) id,nvl(a.thang,0) thang  from";
                sql += " (select a.stations_id,b.don_gia,b.ngay_nhap,b.ghi_chu,b.tong_tien,b.id,b.thang from(";
                sql += " Select B.Id, A.stations_id, A.thang From ( select stations_id,max(thang) thang from fuel_input Where fuel_type_id=1 " + strdetail + " group by stations_id) A, fuel_input B";
                sql += " where fuel_type_id=1  And  A.thang=B.thang And A.stations_id=B.stations_id  and B.nam=" + DateTime.Now.Year;
                sql += " )A,fuel_input b where a.id=b.id ) A,stations B,main_stations C where  b.main_stations_id=c.id and a.stations_id(+)=b.id order by c.name,b.name";
            }
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                FuelStations.Add(new FuelStationsModel { stations_id = Convert.ToInt32(odr["stations_id"].ToString()), name = odr["name"].ToString(), dia_chi = odr["dia_chi"].ToString(), tong_tien = odr["tong_tien"].ToString(), ngay_nhap = odr.IsDBNull(4) ? null : odr.GetDateTime(4).ToString("dd/MM/yyyy"), ghi_chu = odr["ghi_chu"].ToString(), ten_trung_tam = odr["ten_trung_tam"].ToString(), main_stations_id = Convert.ToInt16(odr["main_stations_id"].ToString()), id = Convert.ToInt32(odr["id"].ToString()), thang = Convert.ToInt32(odr["thang"].ToString()) });
            }
            return FuelStations;
        }
        public FuelStationsModel GetFuelStationsBy_Id(string id_)
        {
            FuelStationsModel FuelStations = new FuelStationsModel();
            string sql = "select a.id,b.name ten_tram,b.dia_chi,c.name ten_trung_tam,a.nhap_trong_thang,a.tong_tien,a.ngay_nhap,a.ghi_chu,a.ngay_cap_nhat,a.thang,a.nam,a.stations_id";
            sql += " from fuel_Input a,stations b,main_stations c where ";
            sql += "  a.stations_id=b.id and a.main_stations_id=c.id and a.id=" + id_;
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                FuelStations.id = Convert.ToInt32(odr["id"].ToString());
                FuelStations.name = odr["ten_tram"].ToString();
                FuelStations.dia_chi = odr["dia_chi"].ToString();
                FuelStations.ten_trung_tam = odr["ten_trung_tam"].ToString();
                FuelStations.tong_tien = odr["tong_tien"].ToString();
                FuelStations.nhap_trong_thang = odr["nhap_trong_thang"].ToString();
                FuelStations.ngay_nhap = odr.IsDBNull(6) ? null : odr.GetDateTime(6).ToString("dd/MM/yyyy");
                FuelStations.ghi_chu = odr["ghi_chu"].ToString();
                FuelStations.ngay_cap_nhat = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy");
                FuelStations.thang = Convert.ToInt32(odr["thang"].ToString());
                FuelStations.nam = Convert.ToInt32(odr["nam"].ToString());
                FuelStations.stations_id = Convert.ToInt32(odr["stations_id"].ToString());

            }
            return FuelStations;
        }
        public void updateFuelStationsDetail(FuelStationsModel FuelStations)
        {
            string nhap_trong_thang = FuelStations.nhap_trong_thang;
            string tong_tien = FuelStations.tong_tien;
            if (FuelStations.nhap_trong_thang.IndexOf(',') >= 0)
                nhap_trong_thang=FuelStations.nhap_trong_thang.Replace(',', '.');
            if (FuelStations.tong_tien.IndexOf(',') >= 0)
                tong_tien=FuelStations.tong_tien.Replace(',', '.');
            string sql = "UPDATE fuel_Input set nhap_trong_thang =" + nhap_trong_thang + ", tong_tien=" + tong_tien + ",ngay_cap_nhat=to_date('" + DateTime.Now.ToString("dd/MM/yyyy") + "','dd/mm/yyyy'),ghi_chu='" + FuelStations.ghi_chu + "' where id=" + FuelStations.id;
            General.Lib_ExecuteNonQuery(sql);
        }
        public int checkFuelStations(int stations_id, int thang)
        {
            string sql = "select * from fuel where device_stations_id is null and stations_id=" + stations_id + " and thang>" + thang;
            return General.Lib_CheckExist(sql);
        }
        
        public List<FuelStationsModel> FindFuelStations(string str, string year, string month)
        {
            
            string sql = " select b.id stations_id,b.name,b.dia_chi,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.name ten_trung_tam,c.id main_stations_id,nvl(a.id,0) id,nvl(a.thang,0) thang  from";
            sql += " (select a.stations_id,b.don_gia,b.ngay_nhap,b.ghi_chu,b.tong_tien,b.id,b.thang from(";
            sql += " Select B.Id, A.stations_id, A.thang From ( select stations_id,max(thang) thang from fuel_input Where nam=" + year + " and thang<=" + month + " and fuel_type_id=1 group by stations_id) A, fuel_input B";
            sql += " where fuel_type_id=1  And  A.thang=B.thang And A.stations_id=B.stations_id  and B.nam=" + year;
            sql += " )A,fuel_input b where a.id=b.id ) A,stations B,main_stations C where b.main_stations_id=c.id and a.stations_id(+)=b.id and "+str+" order by c.name,b.name";


            List<FuelStationsModel> FuelStations = new List<FuelStationsModel>();
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                FuelStations.Add(new FuelStationsModel { stations_id = Convert.ToInt32(odr["stations_id"].ToString()), name = odr["name"].ToString(), dia_chi = odr["dia_chi"].ToString(), tong_tien = odr["tong_tien"].ToString(), ngay_nhap = odr.IsDBNull(4) ? null : odr.GetDateTime(4).ToString("dd/MM/yyyy"), ghi_chu = odr["ghi_chu"].ToString(), ten_trung_tam = odr["ten_trung_tam"].ToString(), main_stations_id = Convert.ToInt16(odr["main_stations_id"].ToString()), id = Convert.ToInt32(odr["id"].ToString()), thang = Convert.ToInt32(odr["thang"].ToString()) });
            }
            return FuelStations;
        }
        public void updateFuelStations(FuelStationsModel FuelStations)
        {
            string sql = "INSERT INTO Fuel_Input(id,main_stations_id,stations_id,fuel_type_id,nhap_trong_thang,tong_tien,don_vi_tinh,ngay_nhap,ngay_cap_nhat,ghi_chu,thang,nam)";
            sql += " select nvl((select max(id)+1 from Fuel_Input),1)," + FuelStations.main_stations_id + "," + FuelStations.stations_id + ",1," + FuelStations.nhap_trong_thang + "," + FuelStations.tong_tien + ",'KW'";
            sql += ",to_date('" + FuelStations.ngay_nhap + "','dd/mm/yyyy'),to_date('" + FuelStations.ngay_cap_nhat + "','dd/mm/yyyy')";
            sql += ",'" + FuelStations.ghi_chu + "'," + FuelStations.thang + "," + FuelStations.nam + " from dual";

            General.Lib_ExecuteNonQuery(sql);
        }
    }
    //-----------------------------------------------Fuel Generator-----------------------------------------------------------
    public class FuelGeneratorModel
    {
        public int id { get; set; }
        public string name { get; set; }
        public string dia_chi { get; set; }
        public string ten_trung_tam { get; set; }
        [Required(ErrorMessage = "Xăng nhập trong tháng không được để trống!")]
        //[Range(0, 1000, ErrorMessage = "Xăng nhập trong tháng phải là số và không quá 1000 lít")]
        public string nhap_trong_thang { get; set; }

        [Required(ErrorMessage = "Đơn giá không được để trống!")]
        //[Range(0, 100000, ErrorMessage = "Đơn giá phải là số và không quá 100.000 đ/lít")]
        public string don_gia { get; set; }

        [Required]
        public string ngay_nhap { get; set; }
        public string ngay_cap_nhat { get; set; }
        public string user_cap_nhat { get; set; }
        public string ghi_chu { get; set; }
        public int stations_id { get; set; }
        public int main_stations_id { get; set; }
        public string tong_tien { get; set; }
        public int nam { get; set; }
        public int thang { get; set; }

        public List<FuelGeneratorModel> getGeneratorIndex(string nam, string thang)
        {

            List<FuelGeneratorModel> FuelGenerator = new List<FuelGeneratorModel>();
            string sql = "";
            if (HttpContext.Current.Session != null)
            {
                int quyen_id = Convert.ToInt16(HttpContext.Current.Session["quyen_id"]);
                int trungtam_id = Convert.ToInt16(HttpContext.Current.Session["trungtam_id"]);
                if (quyen_id > 2)
                {
                    sql = "select c.name ten_trung_tam,a.nhap_trong_thang,a.don_gia,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.id main_stations_id,nvl(a.id,0) id,nvl(a.thang,0) thang,nvl(a.nam,0) nam from(";
                    sql += " Select B.Id, B.stations_id,b.nhap_trong_thang,b.don_gia,b.tong_tien,b.main_stations_id,b.ngay_nhap,b.ngay_cap_nhat,b.user_cap_nhat,b.ghi_chu,b.thang,b.nam From(";

                    sql += " select b.main_stations_id,b.thang,max(b.id) id from( select main_stations_id,max(nam) nam from fuel_input where nam=" + nam + " and fuel_type_id>1 group by main_stations_id";
                    sql += " ) a, fuel_input b where b.thang=" + thang + " and a.nam=b.nam and b.fuel_type_id>1 and a.main_stations_id=b.main_stations_id group by b.main_stations_id,b.thang";

                    sql += " ) A, fuel_input B where a.id=b.id)a,main_stations c where c.id="+trungtam_id+" and a.main_stations_id(+)=c.id order by c.name";
                }
                else
                {
                     sql = "select c.name ten_trung_tam,a.nhap_trong_thang,a.don_gia,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.id main_stations_id,nvl(a.id,0) id,nvl(a.thang,0) thang,nvl(a.nam,0) nam from(";
                     sql += " Select B.Id, B.stations_id,b.nhap_trong_thang,b.don_gia,b.tong_tien,b.main_stations_id,b.ngay_nhap,b.ngay_cap_nhat,b.user_cap_nhat,b.ghi_chu,b.thang,b.nam From(";

                     sql += " select b.main_stations_id,b.thang,max(b.id) id from( select main_stations_id,max(nam) nam from fuel_input where nam=" + nam + " and fuel_type_id>1 group by main_stations_id";
                     sql += " ) a, fuel_input b where b.thang=" + thang + " and a.nam=b.nam and b.fuel_type_id>1 and a.main_stations_id=b.main_stations_id group by b.main_stations_id,b.thang";

                     sql += " ) A, fuel_input B where a.id=b.id)a,main_stations c where a.main_stations_id(+)=c.id order by c.name";
                }
            }
            else
            {
                sql = "select c.name ten_trung_tam,a.nhap_trong_thang,a.don_gia,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.id main_stations_id,nvl(a.id,0) id,nvl(a.thang,0) thang,nvl(a.nam,0) nam from(";
                sql += " Select B.Id, B.stations_id,b.nhap_trong_thang,b.don_gia,b.tong_tien,b.main_stations_id,b.ngay_nhap,b.ngay_cap_nhat,b.user_cap_nhat,b.ghi_chu,b.thang,b.nam From(";

                sql += " select b.main_stations_id,b.thang,max(b.id) id from( select main_stations_id,max(nam) nam from fuel_input where nam=" + nam + " and fuel_type_id>1  group by main_stations_id";
                sql += " ) a, fuel_input b where b.thang=" + thang + " and a.nam=b.nam and b.fuel_type_id>1 and a.main_stations_id=b.main_stations_id group by b.main_stations_id,b.thang";

                sql += " ) A, fuel_input B where a.id=b.id)a,main_stations c where a.main_stations_id(+)=c.id order by c.name";
            }
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {

                if (odr["nam"].ToString() != nam)
                {
                    //if(Convert.ToInt32(thang)==1)
                    //{
                    FuelGenerator.Add(new FuelGeneratorModel
                    {
                        ten_trung_tam = odr["ten_trung_tam"].ToString(),
                        nhap_trong_thang = "0",
                        don_gia = "0",
                        tong_tien = "0",   
                        ngay_nhap = odr.IsDBNull(4) ? null : odr.GetDateTime(4).ToString("dd/MM/yyyy"),
                        ghi_chu = "",
                        main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                        thang = Convert.ToInt32(odr["thang"].ToString()),
                        nam = Convert.ToInt32(odr["nam"].ToString())
                    });
                }
                else
                {
                    FuelGenerator.Add(new FuelGeneratorModel
                    {
                        ten_trung_tam = odr["ten_trung_tam"].ToString(),
                        nhap_trong_thang = odr["nhap_trong_thang"].ToString(),
                        don_gia = odr["don_gia"].ToString(),
                        tong_tien = odr["tong_tien"].ToString(),
                        ngay_nhap = odr.IsDBNull(4) ? null : odr.GetDateTime(4).ToString("dd/MM/yyyy"),
                        ghi_chu = "",
                        main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                        thang = Convert.ToInt32(odr["thang"].ToString()),
                        nam = Convert.ToInt32(odr["nam"].ToString())
                    });

                }
            }
            return FuelGenerator;
        }

        public List<FuelGeneratorModel> getGeneratorDetailList(int main_stations_id,string nam, string thang)
        {

            List<FuelGeneratorModel> FuelGenerator = new List<FuelGeneratorModel>();
            string sql = "";
            if (HttpContext.Current.Session != null)
            {
                int quyen_id = Convert.ToInt16(HttpContext.Current.Session["quyen_id"]);
                int trungtam_id = Convert.ToInt16(HttpContext.Current.Session["trungtam_id"]);
                if (quyen_id > 2)
                {
                    sql = "select c.name ten_trung_tam,a.nhap_trong_thang,a.don_gia,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.id main_stations_id,";
                    sql += " nvl(a.id,0) id,nvl(a.thang,0) thang,nvl(a.nam,0) nam from fuel_input a, main_stations c WHERE";
                    sql += " a.fuel_type_id>1 and a.main_stations_id=c.id and c.id=" + trungtam_id + " and a.nam=" + nam + " and a.thang=" + thang+" order by a.ngay_nhap";
                }
                else
                {
                    sql = "select c.name ten_trung_tam,a.nhap_trong_thang,a.don_gia,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.id main_stations_id,";
                    sql += " nvl(a.id,0) id,nvl(a.thang,0) thang,nvl(a.nam,0) nam from fuel_input a, main_stations c WHERE";
                    sql += " a.fuel_type_id>1 and a.main_stations_id=c.id and c.id=" + main_stations_id + " and a.nam=" + nam + " and a.thang=" + thang + " order by a.ngay_nhap";
                }
            }
            else
            {
                sql = "select c.name ten_trung_tam,a.nhap_trong_thang,a.don_gia,nvl(a.tong_tien,0) tong_tien,a.ngay_nhap,a.ghi_chu,c.id main_stations_id,";
                sql += " nvl(a.id,0) id,nvl(a.thang,0) thang,nvl(a.nam,0) nam from fuel_input a, main_stations c WHERE";
                sql += " a.fuel_type_id>1 and a.main_stations_id=c.id and c.id=" + main_stations_id + " and a.nam=" + nam + " and a.thang=" + thang + " order by a.ngay_nhap";
            }
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {

                if (odr["nam"].ToString() != nam)
                {
                    //if(Convert.ToInt32(thang)==1)
                    //{
                    FuelGenerator.Add(new FuelGeneratorModel
                    {
                        id = Convert.ToInt32(odr["id"].ToString()),
                        ten_trung_tam = odr["ten_trung_tam"].ToString(),
                        nhap_trong_thang = "0",
                        don_gia = "0",
                        tong_tien = "0",
                        ngay_nhap = odr.IsDBNull(4) ? null : odr.GetDateTime(4).ToString("dd/MM/yyyy"),
                        ghi_chu = "",
                        main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                        thang = Convert.ToInt32(odr["thang"].ToString()),
                        nam = Convert.ToInt32(odr["nam"].ToString())
                    });
                }
                else
                {
                    FuelGenerator.Add(new FuelGeneratorModel
                    {
                        id = Convert.ToInt32(odr["id"].ToString()),
                        ten_trung_tam = odr["ten_trung_tam"].ToString(),
                        nhap_trong_thang = odr["nhap_trong_thang"].ToString(),
                        don_gia = odr["don_gia"].ToString(),
                        tong_tien = odr["tong_tien"].ToString(),
                        ngay_nhap = odr.IsDBNull(4) ? null : odr.GetDateTime(4).ToString("dd/MM/yyyy"),
                        ghi_chu = odr["ghi_chu"].ToString(),
                        main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                        thang = Convert.ToInt32(odr["thang"].ToString()),
                        nam = Convert.ToInt32(odr["nam"].ToString())
                    });

                }
            }
            return FuelGenerator;
        }
        public void updateGeneratorDetail(FuelGeneratorModel FuelGenerator)
        {
            string don_gia = FuelGenerator.don_gia;
            string nhap_trong_thang = FuelGenerator.nhap_trong_thang;
            if (FuelGenerator.don_gia.IndexOf(',') >= 0) 
                don_gia = FuelGenerator.don_gia.Replace(',', '.');
            if (FuelGenerator.nhap_trong_thang.IndexOf(',') >= 0) 
                nhap_trong_thang = FuelGenerator.nhap_trong_thang.Replace(',', '.');            
            FuelGenerator.tong_tien = (double.Parse(don_gia) * double.Parse(nhap_trong_thang)).ToString();
            string sql = "update Fuel_Input set ngay_cap_nhat=to_date('" + FuelGenerator.ngay_cap_nhat + "','dd/mm/yyyy'),ngay_nhap=to_date('" + FuelGenerator.ngay_nhap + "','dd/mm/yyyy'),";
            sql += "nhap_trong_thang=" + nhap_trong_thang + ",don_gia=" + don_gia + ",tong_tien=" + FuelGenerator.tong_tien + ",ghi_chu='" + FuelGenerator.ghi_chu + "' where id=" + FuelGenerator.id;
            General.Lib_ExecuteNonQuery(sql);
        }
        public void deleteGeneratorDetail(int id)
        {
            string sql = "delete from fuel_input where id="+id;
            General.Lib_ExecuteNonQuery(sql);
        }       

        public void updateFuelGenerator(FuelGeneratorModel FuelGenerator)
        {
            string sql = "INSERT INTO Fuel_Input(id,main_stations_id,stations_id,fuel_type_id,nhap_trong_thang,don_gia,tong_tien,don_vi_tinh,ngay_nhap,ngay_cap_nhat,ghi_chu,thang,nam)";
            sql += " select nvl((select max(id)+1 from Fuel_Input),1)," + FuelGenerator.main_stations_id + "," + FuelGenerator.stations_id + ",2," + FuelGenerator.nhap_trong_thang + ","+FuelGenerator.don_gia+"," + FuelGenerator.tong_tien + ",'lit'";
            sql += ",to_date('" + FuelGenerator.ngay_nhap + "','dd/mm/yyyy'),to_date('" + FuelGenerator.ngay_cap_nhat + "','dd/mm/yyyy')";
            sql += ",'" + FuelGenerator.ghi_chu + "'," + FuelGenerator.thang + "," + FuelGenerator.nam + " from dual";

            General.Lib_ExecuteNonQuery(sql);
        }
        
    }
    public class FuelGeneratorDetailModel
    {
        public int id { get; set; }
        public string name { get; set; }
        public string dia_chi { get; set; }
        public string ten_trung_tam { get; set; }
        public string ngay_cap_nhat { get; set; }
        public string user_cap_nhat { get; set; }
        public string ghi_chu { get; set; }
        public int stations_id { get; set; }
        public int main_stations_id { get; set; }
        public int nam { get; set; }
        public int thang { get; set; }
        public int device_stations_id { get; set; }
        public string ten_tram { get; set; }
        [Required]
        public string ngay_hd { get; set; }
        [Required(ErrorMessage = "Số phút hoạt động không được để trống!")]
        //[Range(0, 1000, ErrorMessage = "Thời gian nhập không quá 1000 phút")]
        public string thoi_gian_hd { get; set; }
        public string ma_he_thong { get; set; }
        public List<FuelGeneratorDetailModel> getFuelGeneratorIndex(string nam, string thang)
        {
            List<FuelGeneratorDetailModel> FuelGenerator = new List<FuelGeneratorDetailModel>();
            string sql = "";
            if (HttpContext.Current.Session != null)
            {
                int quyen_id = Convert.ToInt16(HttpContext.Current.Session["quyen_id"]);
                int trungtam_id = Convert.ToInt16(HttpContext.Current.Session["trungtam_id"]);
                if (quyen_id > 2)
                {
                    sql = "select a.id device_stations_id,a.ma_he_thong,a.name,b.name ten_tram,b.dia_chi,c.name ten_trung_tam,a.ngay_hd,a.thoi_gian_hd,a.ngay_cap_nhat,a.user_cap_nhat,a.ghi_chu,a.stations_id,a.main_stations_id,nvl(a.thang,0) thang,nvl(a.nam,0) nam from";
                    sql += " (select b.id,b.name,b.ma_he_thong,a.ngay_hd,a.thoi_gian_hd,a.ngay_cap_nhat,a.user_cap_nhat,a.ghi_chu,b.stations_id,b.main_stations_id,a.thang,a.nam from(";

                    sql += "Select B.Id, B.stations_id,b.main_stations_id, b.device_stations_id,b.ngay_hd,b.thoi_gian_hd,b.ngay_cap_nhat,b.user_cap_nhat,b.ghi_chu,b.thang,b.nam From";
                    sql += " (select b.device_stations_id,b.thang,max(b.id) id from( select device_stations_id,max(nam) nam from fuel_detail where nam=" + nam + "  group by device_stations_id";
                    sql += " ) a, fuel_detail b where b.thang=" + thang + " and a.nam=b.nam and a.device_stations_id=b.device_stations_id group by b.device_stations_id,b.thang";
                    sql += " ) A, fuel_detail B where a.id=b.id";

                    sql += " ) a,device_stations b where  a.device_stations_id(+)=b.id and b.main_sort_device_id=9)a, stations b, main_stations c";
                    sql += " where a.stations_id=b.id and a.main_stations_id=c.id and c.id=" + trungtam_id + " order by c.name,b.name,a.name";
                }
                else
                {

                    sql = "select a.id device_stations_id,a.ma_he_thong,a.name,b.name ten_tram,b.dia_chi,c.name ten_trung_tam,a.ngay_hd,a.thoi_gian_hd,a.ngay_cap_nhat,a.user_cap_nhat,a.ghi_chu,a.stations_id,a.main_stations_id,nvl(a.thang,0) thang,nvl(a.nam,0) nam from";
                    sql += " (select b.id,b.name,b.ma_he_thong,a.ngay_hd,a.thoi_gian_hd,a.ngay_cap_nhat,a.user_cap_nhat,a.ghi_chu,b.stations_id,b.main_stations_id,a.thang,a.nam from(";

                    sql += "Select B.Id, B.stations_id,b.main_stations_id, b.device_stations_id,b.ngay_hd,b.thoi_gian_hd,b.ngay_cap_nhat,b.user_cap_nhat,b.ghi_chu,b.thang,b.nam From";
                    sql += " (select b.device_stations_id,b.thang,max(b.id) id from( select device_stations_id,max(nam) nam from fuel_detail where nam=" + nam + "  group by device_stations_id";
                    sql += " ) a, fuel_detail b where b.thang=" + thang + " and a.nam=b.nam and a.device_stations_id=b.device_stations_id group by b.device_stations_id,b.thang";
                    sql += " ) A, fuel_detail B where a.id=b.id";

                    sql += " ) a,device_stations b where  a.device_stations_id(+)=b.id and b.main_sort_device_id=9)a, stations b, main_stations c";
                    sql += " where a.stations_id=b.id and a.main_stations_id=c.id order by c.name,b.name,a.name";
                }
            }
            else
            {
                sql = "select a.id device_stations_id,a.ma_he_thong,a.name,b.name ten_tram,b.dia_chi,c.name ten_trung_tam,a.ngay_hd,a.thoi_gian_hd,a.ngay_cap_nhat,a.user_cap_nhat,a.ghi_chu,a.stations_id,a.main_stations_id,nvl(a.thang,0) thang,nvl(a.nam,0) nam from";
                sql += " (select b.id,b.ma_he_thong,b.name,a.ngay_hd,a.thoi_gian_hd,a.ngay_cap_nhat,a.user_cap_nhat,a.ghi_chu,b.stations_id,b.main_stations_id,a.thang,a.nam from(";

                sql += "Select B.Id, B.stations_id,b.main_stations_id, b.device_stations_id,b.ngay_hd,b.thoi_gian_hd,b.ngay_cap_nhat,b.user_cap_nhat,b.ghi_chu,b.thang,b.nam From";
                sql += " (select b.device_stations_id,b.thang,max(b.id) id from( select device_stations_id,max(nam) nam from fuel_detail where nam=" + nam + "  group by device_stations_id";
                sql += " ) a, fuel_detail b where b.thang=" + thang + " and a.nam=b.nam and a.device_stations_id=b.device_stations_id group by b.device_stations_id,b.thang";
                sql += " ) A, fuel_detail B where a.id=b.id";

                sql += " ) a,device_stations b where  a.device_stations_id(+)=b.id and b.main_sort_device_id=9)a, stations b, main_stations c";
                sql += " where a.stations_id=b.id and a.main_stations_id=c.id order by c.name,b.name,a.name";
            }
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                if (odr["nam"].ToString() != nam)
                {
                    FuelGenerator.Add(new FuelGeneratorDetailModel
                    {
                        device_stations_id = Convert.ToInt32(odr["device_stations_id"].ToString()),
                        ma_he_thong = odr["ma_he_thong"].ToString(),
                        name = odr["name"].ToString(),
                        ten_tram = odr["ten_tram"].ToString(),
                        dia_chi = odr["dia_chi"].ToString(),
                        ten_trung_tam = odr["ten_trung_tam"].ToString(),
                        ngay_hd = odr.IsDBNull(6) ? null : odr.GetDateTime(6).ToString("dd/MM/yyyy"),
                        thoi_gian_hd = "0",
                        ngay_cap_nhat = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"),
                        user_cap_nhat = "",
                        ghi_chu = "",
                        stations_id = Convert.ToInt32(odr["stations_id"].ToString()),
                        main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                        thang = Convert.ToInt32(odr["thang"].ToString()),
                        nam = Convert.ToInt32(odr["nam"].ToString())
                    });

                }
                else
                {
                    FuelGenerator.Add(new FuelGeneratorDetailModel
                    {
                        device_stations_id = Convert.ToInt32(odr["device_stations_id"].ToString()),
                        ma_he_thong = odr["ma_he_thong"].ToString(),
                        name = odr["name"].ToString(),
                        ten_tram = odr["ten_tram"].ToString(),
                        dia_chi = odr["dia_chi"].ToString(),
                        ten_trung_tam = odr["ten_trung_tam"].ToString(),
                        ngay_hd = odr.IsDBNull(6) ? null : odr.GetDateTime(6).ToString("dd/MM/yyyy"),
                        thoi_gian_hd = odr["thoi_gian_hd"].ToString(),
                        ngay_cap_nhat = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"),
                        user_cap_nhat = odr["user_cap_nhat"].ToString(),
                        ghi_chu = odr["ghi_chu"].ToString(),
                        stations_id = Convert.ToInt32(odr["stations_id"].ToString()),
                        main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                        thang = Convert.ToInt32(odr["thang"].ToString()),
                        nam = Convert.ToInt32(odr["nam"].ToString())
                    });

                }
            }

            return FuelGenerator;
        }
        public List<FuelGeneratorDetailModel> FindFuelGenerator(string str, string year, string month)
        {
            string sql = "select a.id device_stations_id,a.ma_he_thong,a.name,b.name ten_tram,b.dia_chi,c.name ten_trung_tam,a.ngay_hd,a.thoi_gian_hd,a.ngay_cap_nhat,a.user_cap_nhat,a.ghi_chu,a.stations_id,a.main_stations_id,nvl(a.thang,0) thang,nvl(a.nam,0) nam from";
            sql += " (select b.id,b.ma_he_thong,b.name,a.ngay_hd,a.thoi_gian_hd,a.ngay_cap_nhat,a.user_cap_nhat,a.ghi_chu,b.stations_id,b.main_stations_id,a.thang,a.nam from(";

            sql += "Select B.Id, B.stations_id,b.main_stations_id, b.device_stations_id,b.ngay_hd,b.thoi_gian_hd,b.ngay_cap_nhat,b.user_cap_nhat,b.ghi_chu,b.thang,b.nam From";
            sql += " (select b.device_stations_id,b.thang,max(b.id) id from( select device_stations_id,max(nam) nam from fuel_detail where nam=" + year + "  group by device_stations_id";
            sql += " ) a, fuel_detail b where b.thang=" + month + " and a.nam=b.nam and a.device_stations_id=b.device_stations_id group by b.device_stations_id,b.thang";
            sql += " ) A, fuel_detail B where a.id=b.id";

            sql += " ) a,device_stations b where  a.device_stations_id(+)=b.id and b.main_sort_device_id=9)a, stations c, main_stations b";
            sql += " where a.stations_id=c.id and a.main_stations_id=b.id and " + str + " order by b.name,c.name,a.name";

            List<FuelGeneratorDetailModel> FuelGenerator = new List<FuelGeneratorDetailModel>();
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                if (odr["nam"].ToString() != year.ToString())
                {
                    FuelGenerator.Add(new FuelGeneratorDetailModel
                    {
                        device_stations_id = Convert.ToInt32(odr["device_stations_id"].ToString()),
                        ma_he_thong = odr["ma_he_thong"].ToString(),
                        name = odr["name"].ToString(),
                        ten_tram = odr["ten_tram"].ToString(),
                        dia_chi = odr["dia_chi"].ToString(),
                        ten_trung_tam = odr["ten_trung_tam"].ToString(),
                        ngay_hd = odr.IsDBNull(6) ? null : odr.GetDateTime(6).ToString("dd/MM/yyyy"),
                        thoi_gian_hd = "0",
                        ngay_cap_nhat = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"),
                        user_cap_nhat = "",
                        ghi_chu = "",
                        stations_id = Convert.ToInt32(odr["stations_id"].ToString()),
                        main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                        thang = Convert.ToInt32(odr["thang"].ToString()),
                        nam = Convert.ToInt32(odr["nam"].ToString())
                    });

                }
                else
                {
                    FuelGenerator.Add(new FuelGeneratorDetailModel
                    {
                        device_stations_id = Convert.ToInt32(odr["device_stations_id"].ToString()),
                        ma_he_thong = odr["ma_he_thong"].ToString(),
                        name = odr["name"].ToString(),
                        ten_tram = odr["ten_tram"].ToString(),
                        dia_chi = odr["dia_chi"].ToString(),
                        ten_trung_tam = odr["ten_trung_tam"].ToString(),
                        ngay_hd = odr.IsDBNull(6) ? null : odr.GetDateTime(6).ToString("dd/MM/yyyy"),
                        thoi_gian_hd = odr["thoi_gian_hd"].ToString(),
                        ngay_cap_nhat = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"),
                        user_cap_nhat = odr["user_cap_nhat"].ToString(),
                        ghi_chu = odr["ghi_chu"].ToString(),
                        stations_id = Convert.ToInt32(odr["stations_id"].ToString()),
                        main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                        thang = Convert.ToInt32(odr["thang"].ToString()),
                        nam = Convert.ToInt32(odr["nam"].ToString())
                    });

                }
            }
            return FuelGenerator;
        }
        public void insertFuelGeneratorDetail(FuelGeneratorDetailModel FuelGenerator)
        {
            string sql = "INSERT INTO Fuel_Detail(id,main_stations_id,stations_id,device_stations_id,ngay_hd,thoi_gian_hd,ngay_cap_nhat,ghi_chu,thang,nam)";
            sql += " select nvl((select max(id)+1 from Fuel_Detail),1)," + FuelGenerator.main_stations_id + "," + FuelGenerator.stations_id + "," + FuelGenerator.device_stations_id;
            sql += ",to_date('" + FuelGenerator.ngay_hd + "','dd/mm/yyyy HH24:MI:SS')," + FuelGenerator.thoi_gian_hd + ",to_date('" + FuelGenerator.ngay_cap_nhat + "','dd/mm/yyyy HH24:MI:SS')";
            sql += ",'" + FuelGenerator.ghi_chu + "'," + FuelGenerator.thang + "," + FuelGenerator.nam + " from dual";

            General.Lib_ExecuteNonQuery(sql);
        }

        public void updateFuelGeneratorDetail(FuelGeneratorDetailModel FuelGenerator)
        {
            string sql = "update Fuel_Detail set ngay_cap_nhat=to_date('" + FuelGenerator.ngay_cap_nhat + "','dd/mm/yyyy'),ngay_hd=to_date('" + FuelGenerator.ngay_hd + "','dd/mm/yyyy HH24:MI:SS'),";
            sql += "thoi_gian_hd="+FuelGenerator.thoi_gian_hd+",ghi_chu='"+FuelGenerator.ghi_chu+"' where id="+FuelGenerator.id;
            General.Lib_ExecuteNonQuery(sql);
        }

        //-----------------------------Fuel Generator List-----------------------------------------------------------------

        public List<FuelGeneratorDetailModel> getFuelGeneratorDetailList(string device_stations_id,string nam, string thang)
        {
            List<FuelGeneratorDetailModel> FuelGenerator = new List<FuelGeneratorDetailModel>();
            string sql = "";
            if (HttpContext.Current.Session != null)
            {
                int quyen_id = Convert.ToInt16(HttpContext.Current.Session["quyen_id"]);
                int trungtam_id = Convert.ToInt16(HttpContext.Current.Session["trungtam_id"]);
                if (quyen_id > 2)
                {

                    sql = "select a.id,b.id device_stations_id,b.ma_he_thong,b.name,c.name ten_tram,c.dia_chi,d.name ten_trung_tam,";
                    sql += " a.ngay_hd,a.thoi_gian_hd,a.ngay_cap_nhat,a.user_cap_nhat,a.ghi_chu,a.thang,a.nam,c.id stations_id,d.id main_stations_id";
                    sql+= " from fuel_detail a,device_stations b,stations c, main_stations d";
                    sql+= " where a.main_stations_id=d.id and a.stations_id=c.id ";
                    sql += " and a.device_stations_id=b.id and a.thang=" + thang + " and a.nam=" + nam + " and a.device_stations_id=" + device_stations_id + " and d.id=" + trungtam_id + " order by a.ngay_hd,b.name,c.name,d.name";
                    
                }
                else
                {

                    sql = "select a.id,b.id device_stations_id,b.ma_he_thong,b.name,c.name ten_tram,c.dia_chi,d.name ten_trung_tam,";
                    sql += " a.ngay_hd,a.thoi_gian_hd,a.ngay_cap_nhat,a.user_cap_nhat,a.ghi_chu,a.thang,a.nam,c.id stations_id,d.id main_stations_id";
                    sql += " from fuel_detail a,device_stations b,stations c, main_stations d";
                    sql += " where a.main_stations_id=d.id and a.stations_id=c.id ";
                    sql += " and a.device_stations_id=b.id and a.thang=" + thang + " and a.nam=" + nam + " and a.device_stations_id=" + device_stations_id + " order by a.ngay_hd,b.name,c.name,d.name";
                }
            }
            else
            {
                sql = "select a.id,b.id device_stations_id,b.ma_he_thong,b.name,c.name ten_tram,c.dia_chi,d.name ten_trung_tam,";
                sql += " a.ngay_hd,a.thoi_gian_hd,a.ngay_cap_nhat,a.user_cap_nhat,a.ghi_chu,a.thang,a.nam,c.id stations_id,d.id main_stations_id";
                sql += " from fuel_detail a,device_stations b,stations c, main_stations d";
                sql += " where a.main_stations_id=d.id and a.stations_id=c.id ";
                sql += " and a.device_stations_id=b.id and a.thang=" + thang + " and a.nam=" + nam + " and a.device_stations_id=" + device_stations_id + " order by a.ngay_hd,b.name,c.name,d.name";
            }
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                if (odr["nam"].ToString() != nam)
                {
                    FuelGenerator.Add(new FuelGeneratorDetailModel
                    {
                        id = Convert.ToInt32(odr["id"].ToString()), 
                        device_stations_id = Convert.ToInt32(odr["device_stations_id"].ToString()),
                        ma_he_thong = odr["ma_he_thong"].ToString(),
                        name = odr["name"].ToString(),
                        ten_tram = odr["ten_tram"].ToString(),
                        dia_chi = odr["dia_chi"].ToString(),
                        ten_trung_tam = odr["ten_trung_tam"].ToString(),
                        ngay_hd = odr.IsDBNull(7) ? null : odr.GetDateTime(7).ToString("dd/MM/yyyy H:mm:ss"),
                        thoi_gian_hd = "0",
                        ngay_cap_nhat = odr.IsDBNull(9) ? null : odr.GetDateTime(9).ToString("dd/MM/yyyy"),
                        user_cap_nhat = "",
                        ghi_chu = "",
                        stations_id = Convert.ToInt32(odr["stations_id"].ToString()),
                        main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                        thang = Convert.ToInt32(odr["thang"].ToString()),
                        nam = Convert.ToInt32(odr["nam"].ToString())
                    });

                }
                else
                {
                    FuelGenerator.Add(new FuelGeneratorDetailModel
                    {
                        id = Convert.ToInt32(odr["id"].ToString()), 
                        device_stations_id = Convert.ToInt32(odr["device_stations_id"].ToString()),
                        ma_he_thong = odr["ma_he_thong"].ToString(),
                        name = odr["name"].ToString(),
                        ten_tram = odr["ten_tram"].ToString(),
                        dia_chi = odr["dia_chi"].ToString(),
                        ten_trung_tam = odr["ten_trung_tam"].ToString(),
                        ngay_hd = odr.IsDBNull(7) ? null : odr.GetDateTime(7).ToString("dd/MM/yyyy H:mm:ss"),
                        thoi_gian_hd = odr["thoi_gian_hd"].ToString(),
                        ngay_cap_nhat = odr.IsDBNull(9) ? null : odr.GetDateTime(9).ToString("dd/MM/yyyy"),
                        user_cap_nhat = odr["user_cap_nhat"].ToString(),
                        ghi_chu = odr["ghi_chu"].ToString(),
                        stations_id = Convert.ToInt32(odr["stations_id"].ToString()),
                        main_stations_id = Convert.ToInt32(odr["main_stations_id"].ToString()),
                        thang = Convert.ToInt32(odr["thang"].ToString()),
                        nam = Convert.ToInt32(odr["nam"].ToString())
                    });

                }
            }

            return FuelGenerator;
        }
        public void deleteFuelGeneratorDetail(int id)
        {
            string sql = "DELETE FROM Fuel_Detail WHERE id=" + id;
            General.Lib_ExecuteNonQuery(sql);
        }

    }
}